﻿CREATE FUNCTION [acms].[GetPostSequence]
(
	@PostId int, 
	@PublishDate DateTime
)
RETURNS INT
AS
BEGIN

	--select the count of siblings
	declare @return int
	Select @return = Count(c.Id)
	from acms.Posts a
		inner join acms.Posts b on a.ParentGuid = b.ParentGuid and b.Id <> a.Id
		inner join acms.PostPositions c on b.Id = c.PostId 
			and 
			(
				(@PublishDate is null and c.PublishDate is null)
				or
				(@PublishDate = c.PublishDate)
			)
			and c.Sequence is not null
	where a.Id = @PostId
		and b.Deleted = 0
		
	return @return

END